﻿using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using ZhangBY.Data;
using ZhangBY.Config;
using ZhangBY.Common;
using System.Text.RegularExpressions;
using System.Collections.Generic;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        private string GetSqlstringByPostDatetime(string commandText, DateTime postdatetimeStart, DateTime postdatetimeEnd)
        {
            //日期需要改成参数，以后需要重构！需要先修改后台传递参数方式
            if (!Utils.StrIsNullOrEmpty(postdatetimeStart.ToString()))
                commandText += string.Format(" AND [postdatetime]>='{0}'", postdatetimeStart.ToString("yyyy-MM-dd HH:mm:ss"));

            if (!Utils.StrIsNullOrEmpty(postdatetimeEnd.ToString()))
                commandText += string.Format(" AND [postdatetime]<='{0}'", postdatetimeEnd.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss"));

            return commandText;
        }

        /// <summary>
        /// 插入新产品记录
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="txtTitle">标题</param>
        /// <param name="imgPath">图片路径</param>
        /// <param name="categoriesId">分类ID</param>
        /// <param name="txtContext">简介</param>
        /// <param name="txtAddress">街道</param>
        /// <param name="txtPhone">固话</param>
        /// <param name="txtFax">传真</param>
        /// <param name="txtMobile">手机</param>
        /// <param name="txtArea">地区</param>
        /// <param name="txtWebUrl">网址</param>
        /// <param name="txtProKey">关键字</param>
        /// <param name="txtProDescription">描述</param>
        /// <param name="txtPromulgator">发布者</param>
        /// <param name="blAudit">审核状态</param>
        /// <returns>成功返回非零</returns>
        public int AddConDetailsOrProContext(string userid, string txtTitle, string imgPath, int categoriesId, 
                                             string txtContext, string txtAddress, string txtPhone,string txtFax,
                                             string txtMobile,string txtArea,string txtWebUrl,string txtProKey,
                                             string txtProDescription,string txtPromulgator, bool blAudit)
        {
            DbParameter[] getPar = {
                                      DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar, 50,userid),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NChar,40,txtTitle),
                                      DbHelper.MakeInParam("@imgpath",(DbType)SqlDbType.NChar,300,imgPath),
                                      DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,Utils.GetGuid()),
                                      DbHelper.MakeInParam("@CatId",(DbType)SqlDbType.Int,int.MaxValue,categoriesId),
                                      DbHelper.MakeInParam("@Context",(DbType)SqlDbType.NChar,txtContext.Length,txtContext),
                                      DbHelper.MakeInParam("@Address",(DbType)SqlDbType.NChar,40,txtAddress),
                                      DbHelper.MakeInParam("@Phone",(DbType)SqlDbType.NChar,14,txtPhone),
                                      DbHelper.MakeInParam("@Fax",(DbType)SqlDbType.NChar,14,txtFax),
                                      DbHelper.MakeInParam("@Mobile",(DbType)SqlDbType.NChar,14,txtMobile),
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,50,txtArea),
                                      DbHelper.MakeInParam("@WebUrl",(DbType)SqlDbType.NChar,40,txtWebUrl),
                                      DbHelper.MakeInParam("@ProKey",(DbType)SqlDbType.NChar,40,txtProKey),
                                      DbHelper.MakeInParam("@ProDescription",(DbType)SqlDbType.NChar,60,txtProDescription),
                                      DbHelper.MakeInParam("@Promulgator",(DbType)SqlDbType.NChar,20,txtPromulgator),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,10,blAudit),
                                      DbHelper.MakeOutParam("@OUTNUM",(DbType)SqlDbType.Int,int.MinValue),
                                   };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_insertConDetailsAndProContext]", getPar);
            return Convert.ToInt32((int)getPar[getPar.Length - 1].Value);
        }

        /// <summary>
        /// 更新商铺信息
        /// </summary>
        /// <param name="bodyguid">产品记录唯一ID</param>
        /// <param name="txtTitle">标题</param>
        /// <param name="imgPath">图片路径</param>
        /// <param name="categoriesId">分类ID</param>
        /// <param name="txtContext">简介</param>
        /// <param name="txtAddress">街道</param>
        /// <param name="txtPhone">固话</param>
        /// <param name="txtFax">传真</param>
        /// <param name="txtMobile">手机</param>
        /// <param name="txtArea">地区</param>
        /// <param name="txtWebUrl">网址</param>
        /// <param name="txtProKey">关键字</param>
        /// <param name="txtProDescription">描述</param>
        /// <param name="txtPromulgator">发布者</param>
        /// <param name="blAudit">审核状态</param>
        /// <returns>成功返回非零</returns>
        public int UpDateConDetailsOrProContext(string bodyguid, string txtTitle, string imgPath, int categoriesId,
                                             string txtContext, string txtAddress, string txtPhone, string txtFax,
                                             string txtMobile, string txtArea, string txtWebUrl, string txtProKey,
                                             string txtProDescription, string txtPromulgator, bool blAudit)
        {
            DbParameter[] getPar = {
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NChar,40,txtTitle),
                                      DbHelper.MakeInParam("@imgpath",(DbType)SqlDbType.NChar,300,imgPath),
                                      DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyguid),
                                      DbHelper.MakeInParam("@CatId",(DbType)SqlDbType.Int,int.MaxValue,categoriesId),
                                      DbHelper.MakeInParam("@Context",(DbType)SqlDbType.NChar,txtContext.Length,txtContext),
                                      DbHelper.MakeInParam("@Address",(DbType)SqlDbType.NChar,40,txtAddress),
                                      DbHelper.MakeInParam("@Phone",(DbType)SqlDbType.NChar,14,txtPhone),
                                      DbHelper.MakeInParam("@Fax",(DbType)SqlDbType.NChar,14,txtFax),
                                      DbHelper.MakeInParam("@Mobile",(DbType)SqlDbType.NChar,14,txtMobile),
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,50,txtArea),
                                      DbHelper.MakeInParam("@WebUrl",(DbType)SqlDbType.NChar,40,txtWebUrl),
                                      DbHelper.MakeInParam("@ProKey",(DbType)SqlDbType.NChar,40,txtProKey),
                                      DbHelper.MakeInParam("@ProDescription",(DbType)SqlDbType.NChar,60,txtProDescription),
                                      DbHelper.MakeInParam("@Promulgator",(DbType)SqlDbType.NChar,20,txtPromulgator),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,10,blAudit),
                                      DbHelper.MakeOutParam("@OUTNUM",(DbType)SqlDbType.Int,int.MinValue),
                                   };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_upDateConDetailsAndProContext]", getPar);
            return Convert.ToInt32(getPar[getPar.Length - 1].Value);
        }

        /// <summary>
        /// 获取店铺信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="bodyGuid">BODYGUID</param>
        /// <returns></returns>
        public IDataReader GetContextInfo(string userId, string bodyGuid)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,50,userId),
                                     DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyGuid),
                                 };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getcontextinfo]",getPar); 
        }

        /// <summary>
        /// 根据bodyguid获取店铺信息
        /// </summary>
        /// <param name="bodyGuid">BODYGUID</param>
        /// <returns></returns>
        public IDataReader GetContextInfoByBodyGuid(string bodyGuid)
        {
            DbParameter[] getPar ={ 
                                     DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyGuid),
                                 };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getcontextinfobybodyguid]", getPar); 
        }

        /// <summary>
        /// 列出店铺记录
        /// </summary>
        /// <returns></returns>
        public IDataReader GetDataListContext()
        {
            return DbHelper.ExecuteReader(CommandType.StoredProcedure,"[dbo].[sp_getlistcontextinfo]");
        }

        /// <summary>
        /// 统计行数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public IDataReader GetCountRows(string tableName)
        {
            return DbHelper.ExecuteReader(CommandType.Text, string.Format("SELECT COUNT(*) AS COUNTROWS FROM {0}",tableName));
        }

        /// <summary>
        /// 统计行数
        /// </summary>
        public IDataReader GetCountRows()
        {
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getcountcomdetailsandProContext]");
        }

        /// <summary>
        /// 删除UserContext表以其相关联的表的记录
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="bodyId"></param>
        /// <returns></returns>
        public int DeleteContextRows(string userId, string bodyId)
        {
            DbParameter[] getPar ={
                                 DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,50,userId),
                                 DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyId),
                                 DbHelper.MakeOutParam("@outnum",(DbType)SqlDbType.Int,2),
                                 };
            return DbHelper.ExecuteNonQuery(CommandType.StoredProcedure,"[dbo].[sp_deletecontextrows]",getPar);
        }

        /// <summary>
        /// 删除UserContext表以其相关联的表的记录
        /// </summary>
        /// <param name="bodyId"></param>
        /// <returns></returns>
        public int DeleteContextRows(string bodyId)
        {
            DbParameter[] getPar ={
                                 DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyId),
                                 DbHelper.MakeOutParam("@outnum",(DbType)SqlDbType.Int,2),
                                 };
            return DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_deletecontextrowsbybodyguid]", getPar);
        }

        /// <summary>
        /// 返回图片路径
        /// </summary>
        /// <param name="bodyId"></param>
        /// <returns></returns>
        public IDataReader GetImgPath(string bodyId)
        {
            DbParameter parms = DbHelper.MakeInParam("@bodyguid", (DbType)SqlDbType.NChar, 60, bodyId);
            string commandText = string.Format("SELECT [imgpath] FROM [dbo].[{0}ConDetails] WHERE [bodyguid]=@bodyguid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 返回图片路径
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="bodyId"></param>
        /// <returns></returns>
        public IDataReader GetImgPath(string userId, string bodyId)
        {
            DbParameter[] parms = 
			{
				DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar,60,userId),
				DbHelper.MakeInParam("@bodyguid", (DbType)SqlDbType.NVarChar,60, bodyId),
			};
            string commandText = string.Format("SELECT [imgpath] FROM [dbo].[{0}condetails] WHERE [userid]=@userid AND [bodyguid]=@bodyguid", BaseConfigs.GetTablePrefix);

            return DbHelper.ExecuteReader(CommandType.Text, commandText,parms);
        }
        /// <summary>
        /// 更新图片路径
        /// </summary>
        /// <param name="imgPath"></param>
        /// <param name="bodyguid"></param>
        /// <returns></returns>
        public int UpImgPath(string imgPath,string bodyguid)
        {
            DbParameter[] parms ={
                                     DbHelper.MakeInParam("@imgpath", (DbType)SqlDbType.NChar, 200, imgPath),
                                     DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyguid)
        
        };
            string commandText = string.Format("UPDATE [{0}condetails] SET [imgpath]=@imgpath WHERE [bodyguid]=@bodyguid",BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 修改审核状态
        /// </summary>
        /// <param name="bodyId"></param>
        /// <param name="blValue">状态值</param>
        /// <returns></returns>
        public int SetAudit(string bodyId,bool blValue)
        {
            DbParameter[] getPat = { DbHelper.MakeInParam("@bodyguid",(DbType)SqlDbType.NChar,60,bodyId),
                                     DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,6,blValue)};
            string commTxt = string.Format("UPDATE [{0}condetails] SET [audit]=@audit WHERE [bodyguid]=@bodyguid",BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commTxt,getPat);
        }

       

       
    }
        
}
